--select * FROM FN_LOAD_WIP_FLUSH_BIN_INFO(1033)
ALTER FUNCTION FN_LOAD_WIP_FLUSH_BIN_INFO( @PROCESS_STEP_ID INT )
RETURNS @FN_RESULTS TABLE ( BIN_NUMBER VARCHAR(10),
			PROCESS_STEP_ID INT,
			PART_NUMBER VARCHAR(50),
			BIN_VALUE NUMERIC(5,2) DEFAULT 0 )
AS
BEGIN
	DECLARE @PS_TYPE_NAME VARCHAR(20)
	DECLARE @SORT_PROCESS_STEP_ID INT

	SELECT @PS_TYPE_NAME = PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID
	
	IF ( @PS_TYPE_NAME = 'SORT' )
	 BEGIN
		INSERT INTO @FN_RESULTS( BIN_NUMBER, PROCESS_STEP_ID, PART_NUMBER, BIN_VALUE )
			SELECT ISNULL(WSBT_BIN_NUMBER,'Bin1'), PST.Next_ProcessStep_ID, P.Part_Number, ISNULL(WFBST.Bin_Value,0) FROM Product P
			INNER JOIN Process_Step_Traverse_Table PST ON P.PROCESSSTEP_ID = PST.Next_ProcessStep_ID AND PST.PROCESSSTEP_ID = @PROCESS_STEP_ID
			LEFT OUTER JOIN WIP_STEP_BIN_TABLE WSBT ON WSBT.ProcessStep_ID = PST.ProcessStep_ID AND WSBT_BIN_TYPE = 'Accept' 
			LEFT OUTER JOIN Wip_Flush_Bin_Setup_Table WFBST ON WFBST.Next_ProcessStep_ID = PST.Next_ProcessStep_ID AND WFBST.ProcessStep_ID = @PROCESS_STEP_ID AND Bin_Number = ISNULL(WSBT_BIN_NUMBER,'Bin1')
			ORDER BY CONVERT(INT,SUBSTRING(WSBT_BIN_NUMBER,4,LEN(LTRIM(RTRIM(WSBT_BIN_NUMBER)))))
	 END
	ELSE IF ( @PS_TYPE_NAME = 'Die Bank' )
	 BEGIN
		SELECT TOP 1 @SORT_PROCESS_STEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESS_STEP_ID,0) WHERE PS_TYPE_NAME = 'SORT'
	
		INSERT INTO @FN_RESULTS( BIN_NUMBER, PROCESS_STEP_ID, PART_NUMBER, BIN_VALUE )
			SELECT ABM.Bin_Number, ABM.Assm_ProcessStep_ID, P.Part_Number, ISNULL(WFBST.Bin_Value,0) FROM Assembly_Bin_Mapping ABM 
								INNER JOIN Product P ON P.ProcessStep_ID = ABM.Assm_ProcessStep_ID
								LEFT OUTER JOIN Wip_Flush_Bin_Setup_Table WFBST ON WFBST.Next_ProcessStep_ID = ABM.Assm_ProcessStep_ID AND WFBST.ProcessStep_ID = @PROCESS_STEP_ID AND WFBST.Bin_Number = ABM.Bin_Number
								WHERE ABM.Assm_ProcessStep_ID IN ( SELECT Next_ProcessStep_ID FROM Process_Step_Traverse_Table WHERE ProcessStep_ID = @PROCESS_STEP_ID ) AND ABM.Bin_ProcessStep_ID = @SORT_PROCESS_STEP_ID ORDER BY P.Part_Number

		INSERT INTO @FN_RESULTS ( BIN_NUMBER, PROCESS_STEP_ID, PART_NUMBER, BIN_VALUE )
			SELECT WBST.Bin_Number, WBST.Next_ProcessStep_ID, P.Part_Number, WBST.BIN_VALUE FROM Wip_Flush_Bin_Setup_Table WBST, PRODUCT P
												WHERE WBST.ProcessStep_ID = @PROCESS_STEP_ID
												AND P.ProcessStep_ID = WBST.Next_ProcessStep_ID
												AND WBST.Next_ProcessStep_ID NOT IN ( SELECT PROCESS_STEP_ID FROM @FN_RESULTS WHERE BIN_NUMBER = WBST.BIN_NUMBER )
												AND WBST.Bin_Value > 0
												--AND WBST.BIN_NUMBER NOT IN ( SELECT BIN_NUMBER FROM @FN_RESULTS )
												

	 END
RETURN
	 
END
			